﻿USE ScreeningProcessManagement
GO

---创建菜单视图
IF OBJECT_ID ('MenuView', 'view') IS NOT NULL
DROP VIEW MenuView
GO

CREATE VIEW MenuView
AS 
SELECT MC.Category_ID, MC.Category_Title, MC.Category_Icon, M.Menu_ID, M.Menu_Title, M.Menu_Desc, M.Menu_Icon, R.Role_Name, P.Page_Url, P.Page_ID, MC.TheOrder AS CategoryTheOrder, M.TheOrder
FROM Menus M
INNER JOIN MenuCategories MC
ON M.Category_ID = MC.Category_ID
INNER JOIN Pages P
ON M.Page_ID = P.Page_ID
INNER JOIN RolePages RP
ON P.Page_ID = RP.Page_ID
INNER JOIN Roles R
on RP.Role_ID = R.Role_ID
GO

---创建用户功能视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[UserFunctionalityView]'))
DROP VIEW [dbo].[UserFunctionalityView]
GO

CREATE VIEW [dbo].[UserFunctionalityView]
AS 
SELECT U.[User_ID], F.Functionality_ID, F.Functionality_Code, F.Functionality_Title, F.TheOrder
FROM [Users] U
INNER JOIN [UserRoles] UR
ON U.[User_ID] = UR.[User_ID] 
INNER JOIN RoleFunctionalities RF
ON UR.[Role_ID] = RF.[Role_ID]
INNER JOIN Functionalities F
ON RF.Functionality_ID = F.Functionality_ID 
GO

---创建用户角色视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[UserRoleView]'))
DROP VIEW [dbo].[UserRoleView]
GO

CREATE VIEW [dbo].[UserRoleView]
AS 
SELECT U.[User_ID], R.Role_ID, R.Role_Name, R.Role_Title, R.Role_Desc 
FROM [Users] U
INNER JOIN [UserRoles] UR
ON U.[User_ID] = UR.[User_ID] 
INNER JOIN [Roles] R
ON UR.Role_ID = R.Role_ID 
GO